/*
 * 创建日期 2009-11-29
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.util;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.Element;

import  org.fantasy.common.db.bean.Cell;
import  org.fantasy.common.db.bean.ColumnBean;
import  org.fantasy.common.db.bean.ReportCell;
import  org.fantasy.common.db.bean.ReportRow;
import  org.fantasy.common.db.bean.Row;
import  org.fantasy.common.util.Constants;
import  org.fantasy.common.util.StringUtil;
import  org.fantasy.common.util.XMLUtil;

/**
 * ResultSet转换为RowMap
 * 
 * @author: 王文成
 * @version: 1.0
 * @since 2009-11-29
 */
public class ResultSetRow {

	protected ResultSet rs;

	private int pageSize = 20000;

	protected List<Col> cols = new ArrayList<Col>(16);

	public ResultSetRow(ResultSet rs) throws SQLException {
		this.rs = rs;
		init(rs);
	}

	public ResultSetRow(ResultSet rs, int pageSize) throws SQLException {
		this.rs = rs;
		this.pageSize = pageSize;
		init(rs);
	}

	private void init(ResultSet rs) throws SQLException {
		this.cols = getCols(rs);
	}

	/**
	 * 取得列信息
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public List<Row> getRows() throws Exception {
		List<Row> rows = new ArrayList<Row>();
		while (rs.next()) {
			Row row = new Row();
			for (int i = 0; i < cols.size(); i++) {
				Col col = cols.get(i);
				Object value = rs.getObject(col.getColName());
				Cell cell = new Cell();
				cell.setColClass(col.getCls());
				cell.setColName(col.getColName());
				cell.setName(col.getFieldName());
				cell.setValue(value);
				row.put(col.getFieldName(), cell);
			}
			rows.add(row);
			if (rows.size() >= pageSize) {
				break;
			}
		}
		return rows;
	}

	/**
	 * 取得列信息
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getMaps() throws Exception {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		while (rs.next()) {
			Map<String, Object> row = new HashMap<String, Object>();
			for (int i = 0; i < cols.size(); i++) {
				Col col = cols.get(i);
				Object value = rs.getObject(col.getColName());
				row.put(col.getFieldName(), value);
			}
			list.add(row);
			if (list.size() >= pageSize) {
				break;
			}
		}
		return list;
	}

	/**
	 * 取得列信息
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public List<ReportRow> getReport() throws Exception {
		ReportRow preRow = null;
		List<ReportRow> rows = new ArrayList<ReportRow>();
		while (rs.next()) {
			ReportRow row = new ReportRow();
			ReportCell preCell = null;
			for (int i = 0; i < cols.size(); i++) {
				Col col = cols.get(i);
				Object value = rs.getObject(col.getColName());
				ReportCell cell = new ReportCell();
				cell.setColClass(col.getCls());
				cell.setColName(col.getColName());
				cell.setName(col.getFieldName());
				cell.setValue(value);
				row.put(col.getFieldName(), cell);
				linkX(preCell, cell);
				preCell = cell;
			}
			rows.add(row);
			linkY(preRow, row);
			preRow = row;
			if (rows.size() >= pageSize) {
				break;
			}
		}
		return rows;
	}

	/*
	 * *
	 * 从数据库中提取CLOB类型字段的内容并转换为字符串
	 * 
	 * @param rs 数据库ResultSet,含有CLOB类型的字段
	 * 
	 * @param clobidx 含有CLOB类型字段在ResultSet中的索引
	 * 
	 * @return 取出的字符内容
	 * 
	 * @throws SQLException
	 */
	public String getCLOBContent(ResultSet rs, String columnLabel) throws SQLException {
		Clob clob = rs.getClob(columnLabel);
		if (clob != null) {
			// 9i，10g是从1开始 ，11g是从0开始
			return clob.getSubString((long) 1, (int) clob.length());
		} else {
			return "";
		}
	}

	/*
	 * *
	 * 从数据库中提取CLOB类型字段的内容并转换为字符串
	 * 
	 * @param rs 数据库ResultSet,含有CLOB类型的字段
	 * 
	 * @param clobidx 含有CLOB类型字段在ResultSet中的索引
	 * 
	 * @return 取出的字符内容
	 * 
	 * @throws SQLException
	 */
	public String getBLOBContent(ResultSet rs, String columnLabel) throws SQLException {
		Blob blob = rs.getBlob(columnLabel);
		if (blob != null) {
			return new String(blob.getBytes(0, (int) blob.length()));

		} else {
			return "";
		}
	}

	/**
	 * 横向链接
	 * 
	 * @param preCell
	 * @param cell
	 */
	private void linkX(ReportCell preCell, ReportCell cell) {
		if (preCell != null) {
			preCell.setXNext(cell);
			cell.setXPrevious(preCell);
		}
	}

	/**
	 * 纵向链接
	 * 
	 * @param preRow
	 * @param row
	 */
	private void linkY(ReportRow preRow, ReportRow row) {
		if (preRow != null) {
			List<Cell> cells = row.cells();
			for (int i = 0; i < cells.size(); i++) {
				ReportCell cell = (ReportCell) cells.get(i);
				ReportCell preCell = (ReportCell) preRow.getCell(cell.getName());
				cell.setYPrevious(preCell);
				preCell.setYNext(cell);
			}
		}
	}

	/**
	 * 取得列信息
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public Document getXml() throws Exception {
		Document doc = XMLUtil.createDocument();
		Element root = doc.addElement(Constants.XML_RESULT_ROOT);
		int count = 0;
		while (rs.next()) {
			Element row = XMLUtil.appendChild(root, Constants.XML_RESULT_ROW);
			row.addAttribute("OID", String.valueOf(count));
			for (int i = 0; i < cols.size(); i++) {
				Col col = cols.get(i);
				String name = col.getFieldName();
				// by dengyong 201006021526 modify
				// 增加clob blob 取值处理
				Object value = null;
				if (col.getColType().equals("CLOB")) {
					value = getCLOBContent(rs, col.getColName());
					XMLUtil.appendCDATAChild(row, name, value.toString());
				} else if (col.getColType().equals("BLOB")) {
					value = getBLOBContent(rs, col.getColName());
					XMLUtil.appendCDATAChild(row, name, value.toString());
				} else {
					value = rs.getObject(col.getColName());
					XMLUtil.appendChild(row, name, StringUtil.asString(value));
				}

			}
			count++;
			if (count >= pageSize) {
				break;
			}
		}
		return doc;
	}

	/**
	 * 取得列信息
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected List<Col> getCols(ResultSet rs) throws SQLException {
		ResultSetMetaData metaData = rs.getMetaData();
		int count = metaData.getColumnCount();
		for (int i = 1; i <= count; i++) {
			Col col = new Col();
			String colName = metaData.getColumnName(i);
			col.setColName(colName);
			col.setFieldName(StringUtil.getFieldString(colName));
			col.setCls(loadClass(metaData.getColumnClassName(i)));
			col.setColType(metaData.getColumnTypeName(i));
			cols.add(col);
		}
		return cols;
	}
	
	/**
	 * 取得MetaData
	 * @return
	 */
	public List<ColumnBean> getMetaData() throws Exception{
		List<ColumnBean> cols = new ArrayList<ColumnBean>();
		ResultSetMetaData rsmd = rs.getMetaData();
		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			String name = rsmd.getColumnName(i);
			String type = rsmd.getColumnClassName(i);
			String comment = rsmd.getColumnLabel(i);
			int size = rsmd.getColumnDisplaySize(i);
			int n = rsmd.isNullable(i);
			int scale = rsmd.getScale(i);
			boolean nullable = (n == 0 ? false : true);
			ColumnBean bean = new ColumnBean(name, type, size, nullable, scale, comment);
			cols.add(bean);
		}
		return cols;
	}

	/**
	 * <p>
	 * Loads and returns the <code>Class</code> of the given name. By default, a
	 * load from the thread context class loader is attempted. If there is no
	 * such class loader, the class loader used to load this class will be
	 * utilized.
	 * </p>
	 * 
	 * @exception SQLException
	 *                if an exception was thrown trying to load the specified
	 *                class
	 */
	protected Class loadClass(String className) throws SQLException {

		try {
			ClassLoader cl = Thread.currentThread().getContextClassLoader();
			if (cl == null) {
				cl = this.getClass().getClassLoader();
			}
			return (cl.loadClass(className));
		} catch (Exception e) {
			throw new SQLException("Cannot load column class '" + className + "': " + e);
		}

	}

	private static class Col {
		private String fieldName;

		private String colName;
		// by dengyong 201006021526 modify
		// 增加列值类型
		private String colType;

		private Class cls;

		public Class getCls() {
			return cls;
		}

		public void setCls(Class cls) {
			this.cls = cls;
		}

		public String getColName() {
			return colName;
		}

		public void setColName(String colName) {
			this.colName = colName;
		}

		public String getFieldName() {
			return fieldName;
		}

		public void setFieldName(String fieldName) {
			this.fieldName = fieldName;
		}

		// by dengyong 201006021526 modify
		// 增加列值类型
		public void setColType(String colType) {
			this.colType = colType;
		}

		public String getColType() {
			return colType;
		}
	}
}
